var express = require('express');
var router = express.Router();
// let ejs = require('ejs');
let mysql = require('mysql');


//配置mysql数据库
let config = {
  host: "localhost",
  port: 3306,
  user: "root",
  password: "root",
  database: "student",
}

//连接数据库
let connection = mysql.createConnection(config);

//判断是否连接成功
connection.connect((err) => {
  if (err) {
      console.log("连接数据库失败")
  } else {
      console.log("数据库连接成功")
  }
})


/* GET home page. */
router.get('/index', (req,res)=>{
  //查询数据
  let sql = `select * from stu_username`;
  connection.query(sql, (err, doc) => {
      if (err) {
          console.log("查询失败")
      } else {
          //console.log(doc);
          res.render("view/index.html", { doc: doc });
      }
  })
});

//渲染添加页面
router.get("/add", (req, res) => {
  res.render("view/add.html")
})

//处理表单数据
router.post("/add", (req, res) => {
  //获取表单数据
  //console.log(req.body)
  let { name, sex, age } = req.body;
  let sql = `insert into stu_username (name,sex,age) value ("${name}","${sex}",${age})`;
  connection.query(sql, (err) => {
      if (err) {
          res.render("view/wait.html", {
              wait: 2,//等待时间
              content: "添加失败",
              href: "/add"
          })
      } else {
          res.render("view/wait.html", {
              wait: 2,//等待时间
              content: "添加成功",
              href: "/index"
          })
      }
  })
})



//删除数据
router.get("/del", (req, res) => {
  //更加id删除 
  //获取当前点击的id值
  let id = req.query.id;
  //console.log(id);
  let sql = `delete from stu_username where id=${id}`;
  connection.query(sql, (err) => {
      if (err) {
          //res.send("删除失败")
          res.render("view/wait.html", {
              wait: 2,//等待时间
              content: "删除失败",
              href: "/index"
          })
      } else {
          //res.send("删除成功")
          res.render("view/wait.html", {
              wait: 2,//等待时间
              content: "删除成功",
              href: "/index"
          })
      }
  })
})


//修改数据  查询对应的数据
router.get("/update", (req, res) => {
  //查询数据
  let id = req.query.id;
  let sql = `select * from stu_username where id=${id}`;
  connection.query(sql, (err, doc) => {
      if (err) {
          console.log("查询数据失败")
      } else {
          res.render("view/edit.html", { result: doc })
      }

  })

})



//处理表单
router.post("/update", (req, res) => {
  //获取表单提交的数据
  let { name, sex, age, id } = req.body;
  //console.log(name, sex, age, id);
  let sql = `update stu_username set name="${name}",sex="${sex}",age=${age} where id=${id}`
  //console.log(sql);
  connection.query(sql, (err) => {
      if (err) {
          //res.send("删除失败")
          res.render("view/wait.html", {
              wait: 2,//等待时间
              content: "修改失败",
              href: "/update"
          })
      } else {
          //res.send("删除成功")
          res.render("view/wait.html", {
              wait: 2,//等待时间
              content: "修改成功",
              href: "/index"
          })
      }
  })
})





module.exports = router;
